<?php

namespace app\admin\controller\serial;

use app\admin\model\Assistant;
use app\admin\model\halt\Cate;
use app\common\controller\Backend;
use app\common\model\Agent;
use app\admin\model\Githouse;
use app\common\model\Area;
use EasyWeChat\Factory;
use PHPExcel;
use PhpOffice\PhpSpreadsheet\Calculation\Exception;
use think\Db;

/**
 * 产品生产
 *
 * @icon fa fa-circle-o
 */
class Serials extends Backend
{
    
    /**
     * Serial模型对象
     * @var \app\common\model\Serial
     */
    protected $model = null;
    protected $agentModel = null;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\common\model\Serial;
        $this->agentModel = new Agent();
        $this->modelGithouse = new Githouse();
        $this->view->assign("TypeList", $this->model->getTypeList());
    }

    /**
     * 查看
     */
    public function index()
    {
        $this->searchFields = 'code,order_sn';
        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }

            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $total = $this->model
                ->where($where)
                ->order($sort, $order)
                ->count();

            $list = $this->model
                ->where($where)
                ->order($sort, $order)
                ->limit($offset, $limit)
                ->select();
            $list = collection($list)->toArray();
            foreach ($list as &$value){
                $value['type'] = $this->model->getTypeTextAttr($value['type'],'');
            }
            $result = array("total" => $total, "rows" => $list);

            return json($result);
        }
        return $this->view->fetch();
    }
    //产品发货
    public function delivergoods(){
        if($this->request->isAjax()){

            //接收序列号
            $row = $this->request->request('row/a');
            //封装数组
            $codeListArr = explode("\n", $row['code']);
            //去除空数组
            $ptn = "/\S+/i";
            $codeListArr = preg_grep($ptn, $codeListArr);
            if (!$codeListArr) $this->error('请输入序列号信息');
            if($row['action']==1){
                //获取分公司信息
                $agent = Githouse::get(['gname'=>$row['githouse']]);
                $agent->agent_region = $agent->address;
            }else{
                //获取代理商信息
                $agent = Agent::get(['agent_name'=>$row['nameAgent']]);
            }
            if(!$agent)$this->error('经销商或分公司信息不存在');
            if(!$agent->agent_region)$this->error('经销商未设置代理地区或分公司无地区信息');
            //开启事务
            $num = 0;
            Db::startTrans();
            $isCancel = [];
            $isRegister = [];
            $send = [];
            $codeNull = [];
            foreach ($codeListArr as $ve){
                //去除符号和空格
                $ve = str_replace("-", "", str_replace(" ",'', trim($ve)));
                preg_match_all('/\d+/',$ve,$arr);
                $ve = join('',$arr[0]);
                $codeArr =$this->model->where(['code'=>$ve])->find();
                if(!$codeArr && strlen($ve)==13 && substr($ve, 0, 1 )==2){
                    $this->model->create([
                        'code'=>$ve,
                        'product'=>'35',
                        'kemu'=>'12',
                        'banben'=>'16',
                        'type'=>'8,31,37',
                    ]);
                    $codeArr =$this->model->where(['code'=>$ve])->find();
                }
                $newkeyStr= substr($ve, 0, 3);
                $newkey= $newkeyStr . '-' . substr($ve, 3, 5). '-' . substr($ve, 8);
                if(!$codeArr){
                    $codeNull[] = $newkey;
                    continue;
                }
                if($codeArr['is_cancel']==1)
                {
                    $isCancel[] = $newkey;
                    continue;
                }
                if($codeArr['is_register']==1){
                    $isRegister[] = $newkey;
                    continue;
                }
                if($codeArr['send']==1){
                    $send[] = $newkey;
                    continue;
                }
//                if(!$codeArr)$this->error('序列号不存在' .$newkey);
//                if($codeArr['is_cancel']==1)$this->error('序列号已作废:' .$newkey);
//                if($codeArr['is_register']==1)$this->error('序列号已被用户激活:' .$newkey);
//                if($codeArr['send']==1)$this->error('序列号已发货' .$newkey);
                if($row['action']==1){
                    //分公司
                    $codeArr->branch_uid = $agent->id;
                }else{
                    //代理商
                    $codeArr->dealer_uid = $agent->id;
                }
                $codeArr->send = 1;
                $codeArr->sendtime = time();
                $codeArr->fahuo =  $agent->agent_region;
                $num += $codeArr->save();
            }
            if(count($isCancel)>0 || count($isRegister) >0 || count($send) > 0 || count($codeNull) > 0)
            {
                Db::rollback();
                $this->error(__('数据有错误！'),'',['isCancel'=>$isCancel,'Register'=>$isRegister,'send'=>$send,'codeNull'=>$codeNull]);
            }
            if ($num == 0) {
                Db::rollback();
                $this->error(__('没有数据可以更新！'));
            }
            // 提交事务
            Db::commit();
            $this->success('发货成功' . count($codeListArr) . '个');

        }
        return $this->view->fetch();
    }
    //产品调货
    public function send(){
        if($this->request->isAjax()){
            //接收序列号
            $row = $this->request->request('row/a');
            //封装数组
            $codeListArr = explode("\n", $row['code']);
            //去除空数组
            $ptn = "/\S+/i";
            $codeListArr = preg_grep($ptn, $codeListArr);
            if (!$codeListArr) $this->error('请输入序列号信息');
            $area='';
            $province='';
            $city='';
            //获取地区信息
            $diqu = array('省','自治区','壮族','回族','维吾尔','特别行政区','北京','天津','重庆','上海');
            if($this->request->post("province") && $this->request->post("city") && $this->request->post("area")){
                $province = str_replace($diqu,'',Area::get(['id'=>$this->request->post("province")])->name);
                $city = str_replace('市','',Area::get(['id'=>$this->request->post("city")])->name);
                $area = Area::get(['id'=>$this->request->post("area")])->name;
            }elseif($this->request->post("province") && $this->request->post("city")){
                $province = str_replace($diqu,'',Area::get(['id'=>$this->request->post("province")])->name);
                $city = Area::get(['id'=>$this->request->post("city")])->name;
                $area='';
            }elseif($this->request->post("province")){
                $province = str_replace($diqu,'',Area::get(['id'=>$this->request->post("province")])->name);
                $city='';
                $area='';
            }
            //开启事务
            Db::startTrans();
            foreach ($codeListArr as $v) {
                //去除符号和空格
                $v = str_replace("-", "", str_replace(" ", '', trim($v)));
                preg_match_all('/\d+/',$v,$arr);
                $v = join('',$arr[0]);
                $codeArr = $this->model->where(['code' => $v])->find();
                if (!$codeArr) $this->error('序列号不存在' . $v);
                if ($codeArr['is_cancel'] == 1) $this->error('序列号已作废:' . $v);
                if ($codeArr['is_register'] == 1) $this->error('序列号已被用户激活:' . $v);
                if ($codeArr['send'] != 1) $this->error('序列号未发货:' . $v);
                $data['send'] = 1;
                $data['kaoshi']= $province;
                $data['fahuo'] = $province.$city.$area;

                $send_show = $this->model->where(['code' => $v])->update($data);
                if (!$send_show) {
                    Db::rollback();
                    $this->error('调货失败');
                }
            }
            // 提交事务
            Db::commit();
            $this->success('调货成功' . count($codeListArr) . '个');

        }
        return $this->view->fetch();
    }
    //网销发货
    public function kefusend(){
        if($this->request->isAjax()){

            //接收序列号
            $row = $this->request->request('row/a');
            //封装数组
            $codeListArr = explode("\n", $row['code']);
            //去除空数组
            $ptn = "/\S+/i";
            $codeListArr = preg_grep($ptn, $codeListArr);
            if (!$codeListArr) $this->error('请输入序列号信息');
            $area='';
            $province='';
            $city='';
            //获取地区信息
            $diqu = array('省','自治区','壮族','回族','维吾尔','特别行政区','北京','天津','重庆','上海');
            if($this->request->post("province") && $this->request->post("city") && $this->request->post("area")){
                $province = str_replace($diqu,'',Area::get(['id'=>$this->request->post("province")])->name);
                $city = str_replace('市','',Area::get(['id'=>$this->request->post("city")])->name);
                $area = Area::get(['id'=>$this->request->post("area")])->name;
            }elseif($this->request->post("province") && $this->request->post("city")){
                $province = str_replace($diqu,'',Area::get(['id'=>$this->request->post("province")])->name);
                $city = Area::get(['id'=>$this->request->post("city")])->name;
                $area='';
            }elseif($this->request->post("province")){
                $province = str_replace($diqu,'',Area::get(['id'=>$this->request->post("province")])->name);
                $city='';
                $area='';
            }
            //获取代理商信息
            $agent = Agent::get(['agent_name'=>$row['nameAgent']]);

            //开启事务
            $num = 0;
            Db::startTrans();
            $isCancel = [];
            $isRegister = [];
            $send = [];
            $codeNull = [];
            foreach ($codeListArr as $ve){
                //去除符号和空格
                $ve = str_replace("-", "", str_replace(" ",'', trim($ve)));
                preg_match_all('/\d+/',$ve,$arr);
                $ve = join('',$arr[0]);
                $codeArr =$this->model->where(['code'=>$ve])->find();
                if(!$codeArr && strlen($ve)==13 && substr($ve, 0, 1 )==2){
                   $this->model->create([
                        'code'=>$ve,
                        'product'=>'35',
                        'kemu'=>'12',
                        'banben'=>'16',
                        'type'=>'8,31,37',
                    ]);
                    $codeArr =$this->model->where(['code'=>$ve])->find();
                }
//                if(!$codeArr)$this->error('序列号不存在' .$ve);
//                if($codeArr['is_cancel']==1)$this->error('序列号已作废:' .$ve);
//                if($codeArr['is_register']==1)$this->error('序列号已被用户激活:' .$ve);
//                if($codeArr['send']==1)$this->error('序列号已发货' .$ve);
                $newkeyStr= substr($ve, 0, 3);
                $newkey= $newkeyStr . '-' . substr($ve, 3, 5). '-' . substr($ve, 8);
                if(!$codeArr){
                    $codeNull[] = $newkey;
                    continue;
                }
                if($codeArr['is_cancel']==1)
                {
                    $isCancel[] = $newkey;
                    continue;
                }
                if($codeArr['is_register']==1){
                    $isRegister[] = $newkey;
                    continue;
                }
                if($codeArr['send']==1){
                    $send[] = $newkey;
                    continue;
                }
                //代理商
//                $data['dealer_uid'] = $agent->id;
//                $data['send'] = 1;
//                $data['sendtime'] = time();
//                $data['kaoshi']= $province;
//                $data['fahuo'] = $province.$city.$area;
                $codeArr->dealer_uid=$agent->id;
                $codeArr->send=1;
                $codeArr->sendtime=time();
                $codeArr->kaoshi=$province;
                $codeArr->fahuo=$province.$city.$area;
                $num += $codeArr->save();
            }
            if(count($isCancel)>0 || count($isRegister) >0 || count($send) > 0|| count($codeNull) > 0)
            {
                Db::rollback();
                $this->error(__('数据有错误！'),'',['isCancel'=>$isCancel,'Register'=>$isRegister,'send'=>$send,'codeNull'=>$codeNull]);
            }
            if ($num == 0) {
                Db::rollback();
                $this->error(__('没有数据可以更新！'));
            }
            // 提交事务
            Db::commit();
            $this->success('发货成功' . count($codeListArr) . '个');

        }
        return $this->view->fetch();
    }
    //产品分类调整
    public function fenleis()
    {
        if($this->request->isAjax()){
            //接收序列号
            $row = $this->request->request('row/a');
            if(!$row['product'] || !$row['kemu'])
            {
                $this->error('请选择产品分类');
            }
            //封装数组
            $codeListArr = explode("\n", $row['code']);
            //去除空数组
            $ptn = "/\S+/i";
            $codeListArr = preg_grep($ptn, $codeListArr);
            if (!$codeListArr) $this->error('请输入序列号信息');
            //开启事务
            Db::startTrans();
            foreach ($codeListArr as $v) {
                //去除符号和空格
                $v = str_replace("-", "", str_replace(" ", '', trim($v)));
                preg_match_all('/\d+/',$v,$arr);
                $v = join('',$arr[0]);
                $codeArr = $this->model->where(['code' => $v])->find();
                if (!$codeArr) $this->error('序列号不存在' . $v);
                if ($codeArr['is_cancel'] == 1) $this->error('序列号已作废:' . $v);
                if ($codeArr['is_register'] == 1) $this->error('序列号已被用户激活:' . $v);
                $data['type'] = $row['product'].','.$row['kemu'].','.$row['banben'];
                if($row['banben']){
                    $banben = Cate::get($row['banben']);
                }else{
                    $banben = Cate::get($row['kemu']);
                }
                $data['product'] = $banben->product;
                $data['kemu'] = $banben->kemu;
                $data['banben'] = $banben->banben;
                $send_show = $this->model->where(['code' => $v])->update($data);
                if (!$send_show) {
                    Db::rollback();
                    $this->error('产品分类调整失败');
                }
            }
            // 提交事务
            Db::commit();
            $this->success('产品分类调整成功' . count($codeListArr) . '个');

        }
        return $this->view->fetch();
    }
    //产品作废调整
    public function tovoid()
    {
        if($this->request->isAjax()){
            //接收序列号
            $row = $this->request->request('row/a');
            //封装数组
            $codeListArr = explode("\n", $row['code']);
            //去除空数组
            $ptn = "/\S+/i";
            $codeListArr = preg_grep($ptn, $codeListArr);
            if (!$codeListArr) $this->error('请输入序列号信息');
            //开启事务
            Db::startTrans();
            foreach ($codeListArr as $v) {
                //去除符号和空格
                $v = str_replace("-", "", str_replace(" ", '', trim($v)));
                preg_match_all('/\d+/',$v,$arr);
                $v = join('',$arr[0]);
                $codeArr = $this->model->where(['code' => $v])->find();
                if (!$codeArr) $this->error('序列号不存在' . $v);
                if ($codeArr['is_cancel'] == 1) $this->error('序列号已作废:' . $v);
                if ($codeArr['is_register'] == 1) $this->error('序列号已被用户激活:' . $v);
                if ($codeArr['send'] != 1) $this->error('序列号未发货:' . $v);
                $data['is_cancel'] = 1;
                $send_show = $this->model->where(['code' => $v])->update($data);
                if (!$send_show) {
                    Db::rollback();
                    $this->error('产品作废调整失败');
                }
            }
            // 提交事务
            Db::commit();
            $this->success('产品作废成功' . count($codeListArr) . '个');

        }
        return $this->view->fetch();
    }
    //产品退货
    public function returngoods()
    {
        if($this->request->isAjax()){
            //接收序列号
            $row = $this->request->request('row/a');
            //封装数组
            $codeListArr = explode("\n", $row['code']);
            //去除空数组
            $ptn = "/\S+/i";
            $codeListArr = preg_grep($ptn, $codeListArr);
            if (!$codeListArr) $this->error('请输入序列号信息');
            //开启事务
            Db::startTrans();
            foreach ($codeListArr as $v) {
                //去除符号和空格
                $v = str_replace("-", "", str_replace(" ", '', trim($v)));
                preg_match_all('/\d+/',$v,$arr);
                $v = join('',$arr[0]);
                $codeArr = $this->model->where(['code' => $v])->find();
                if (!$codeArr) $this->error('序列号不存在' . $v);
                if ($codeArr['is_cancel'] == 1) $this->error('序列号已作废:' . $v);
                $data = array(
                    'fahuo'        => '',
                    'kaoshi'       => '',
                    'uid' =>'',
                    'send'          =>0,
                    'dealer_uid' => 0,
                    'branch_uid' => 0,
                    'sendtime'=>0,
                    'action_time'=>0,
                    'is_register'=>0,
                );
                $send_show = $this->model->where(['code' => $v])->update($data);
                if (!$send_show) {
                    Db::rollback();
                    $this->error('产品退货调整失败');
                }
            }
            // 提交事务
            Db::commit();
            $this->success('产品退货成功' . count($codeListArr) . '个');

        }
        return $this->view->fetch();
    }

    /**
     * 生产序列号
     * @return string
     * @throws \think\Exception
     * @throws \think\exception\DbException
     */
    public function add()
    {
        if($this->request->isAjax())
        {
            $row = $this->request->request('row/a');
            if(!$row['product'] || !$row['kemu'] )
            {
                $this->error('请选择产品分类');
            }
            if($row['action'] == 2 && !$row['userid'])
            {
                $this->error('请选择企业微信收取人');
            }
            if($row['number'] < 1 || !$row['number'] || $row['number'] > 100000)
            {
                $this->error('请填写大于0的数量,或数量不要大于10万');
            }
            if($row['banben']){
                $banben = Cate::get($row['banben']);
                $product = Cate::get($row['product']);
                $kemu = Cate::get($row['kemu']);

            }else{
                $banben = Cate::get($row['kemu']);
                $product = Cate::get($row['product']);
                $kemu = Cate::get($row['kemu']);
            }

            $data = [];
            for ($i=1;$i<=$row['number'];$i++)
            {
                $data[$i] = [
                    'code'=>'2'.$this->random(12),
                    'product'=>$banben->product,
                    'kemu'=>$banben->kemu,
                    'banben'=>$banben->banben,
                    'kanwu_type'=>$row['kanwu_type'],
                    'type'=>rtrim($row['product'].','.$row['kemu'].','.$row['banben'],','),
                ];
            }
            // 启动事务
            Db::startTrans();
            try{
                $count = ceil(count($data) / 10);
                for ($i=1;$i<=$count;$i++){
                    $offset=($i -1)*(10);
                    $array=array_slice($data,$offset,10);
                    $this->model->saveAll($array);
                }
            } catch (\Exception $e) {
                // 回滚事务
                Db::rollback();
                $this->error('数据重复。','',$e->getMessage());
            }
            $url = '';
            $res = false;
            // 提交事务
            Db::commit();
            //下载到本地
            if($row['action'] == 1)
            {
                if($row['banben']){
                    $url = $this->export($data,'',15000,$name = $product->name.$kemu->name.$banben->name);
                }else{
                    $url = $this->export($data,'',15000,$name = $product->name.$kemu->name);
                }

            }else{

                $this->export($data,$row['userid'],15000,$name = $product->name.$kemu->name.$banben->name);
            }
            if($this->request->request('res') == 2)
            {
                $this->error('序列号生成成功！',$url);
            }
            $this->success('序列号生成成功！',$url);

        }
        return $this->view->fetch();
    }

    public function export(array $list,$userid = null,$limit=1000,$name='')
    {
        $objPHPExcel = new PHPExcel();
        $count = ceil(count($list) / $limit);
        $row = $objPHPExcel->setactivesheetindex(0);
        //列为文本
        $row->getStyle('A')->getNumberFormat()
            ->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        for ($i=1;$i<=$count;$i++){
            $row->setTitle("$i");
            $offset = ($i-1) * $limit;
            $array = array_slice($list,$offset,$limit);
            foreach ($array as $k => $v) {
                $newkeyStr= substr($v['code'], 0, 3);
                $newkey= $newkeyStr . '- ' . substr($v['code'], 3, 5). '- ' . substr($v['code'], 8);
                $row->setcellvalue('A'.($k+1), ''.$newkey.'');
                unset($newkeyStr,$newkey);
                $row->getColumnDimension('A')->setWidth(30);
            }
            if($i <= $count-1)
            {
                //创建一个新的工作空间(sheet)
                $objPHPExcel->createSheet();
                $row = $objPHPExcel->setactivesheetindex($i);
                $row->setTitle("$i");
                //列为文本
                $row->getStyle('A')->getNumberFormat()
                    ->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
            }
        }
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $date = date('Y-m-d H-i-s',time());
        $filename = $this->auth->username.'-'.$name.'-'.$date.'.xlsx';
        $objWriter->save('./agent/xlsx/'.$filename);
        //添加操作日志
        $data = [
            'text' => $this->auth->username.'生成'.$name.'/'.count($list).'个',
            'url' => '/agent/xlsx/'.$filename,
        ];
        //上传到企业微信临时文件
        if($userid)
        {
            $config = Assistant::config();
            $app = Factory::work($config);
            $path = ROOT_PATH . 'public/agent/xlsx/'.$filename;
            $res = $app->media->uploadFile($path);
            //发送文件给操作人
            if($res['errcode'] == 0 ){
//                unlink($path);
                $token = $app->access_token->getToken();//获取企业微信token
                $url = "https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token={$token['access_token']}";
                $params = [
                    'touser'=>$userid,
                    'msgtype'=>'file',
                    'agentid'=>$config['agent_id'],
                    'file'=>[
                        'media_id'=>$res['media_id']
                    ],
                    'safe'=>0
                ];
                $response = \fast\Http::post($url,json_encode($params));
                $data['media_id'] = $res['media_id'];
                $data['text'] .='发送给'.$this->auth->username;
                $response = json_decode($response,true);
                if($response['errcode'] != 0)
                {
                    return false;
                }
            }
            $log = \app\common\model\SerialLog::create($data);
            return $log->id;
        }
        return $data['url'];
    }
    
    /**
     * 生产序列号
     * @param $length
     * @param string $chars
     * @return string
     */
    protected function random($length, $chars = '01234567893216549870') {
        $hash = '';
        $max = strlen($chars) - 1;
        for($i = 0; $i < $length; $i++) {
            $hash .= $chars[mt_rand(0, $max)];
        }
        return $hash;
    }

    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */
    /**
     * 生成查询所需要的条件,排序方式
     * @param mixed $searchfields 快速查询的字段
     * @param boolean $relationSearch 是否关联查询
     * @return array
     */
    protected function buildparams($searchfields = null, $relationSearch = null)
    {
        $searchfields = is_null($searchfields) ? $this->searchFields : $searchfields;
        $relationSearch = is_null($relationSearch) ? $this->relationSearch : $relationSearch;
        $search = $this->request->get("search", '');
        $filter = $this->request->get("filter", '');
        $op = $this->request->get("op", '', 'trim');
        $sort = $this->request->get("sort", "id");
        $order = $this->request->get("order", "DESC");
        $offset = $this->request->get("offset", 0);
        $limit = $this->request->get("limit", 0);
        $filter = (array)json_decode($filter, TRUE);
        $op = (array)json_decode($op, TRUE);
        $filter = $filter ? $filter : [];
        $where = [];
        $tableName = '';
        if ($relationSearch) {
            if (!empty($this->model)) {
                $name = \think\Loader::parseName(basename(str_replace('\\', '/', get_class($this->model))));
                $tableName = $name . '.';
            }
            $sortArr = explode(',', $sort);
            foreach ($sortArr as $index => & $item) {
                $item = stripos($item, ".") === false ? $tableName . trim($item) : $item;
            }
            unset($item);
            $sort = implode(',', $sortArr);
        }
        $adminIds = $this->getDataLimitAdminIds();
        if (is_array($adminIds)) {
            $where[] = [$tableName . $this->dataLimitField, 'in', $adminIds];
        }
        if ($search) {
            $searcharr = is_array($searchfields) ? $searchfields : explode(',', $searchfields);
            foreach ($searcharr as $k => &$v) {
                $v = stripos($v, ".") === false ? $tableName . $v : $v;
            }
            unset($v);
            //过滤 -
            $search = str_replace('-','',$search);
            $search = str_replace(' ','',$search);
            $where[] = [implode("|", $searcharr), "LIKE", "%{$search}%"];
        }
        foreach ($filter as $k => $v) {
            $sym = isset($op[$k]) ? $op[$k] : '=';
            if (stripos($k, ".") === false) {
                $k = $tableName . $k;
            }
            if($k == 'code')
            {
                $v = str_replace('-','',$v);
                $v = str_replace(' ','',$v);
            }
            $v = !is_array($v) ? trim($v) : $v;
            $sym = strtoupper(isset($op[$k]) ? $op[$k] : $sym);
            switch ($sym) {
                case '=':
                case '!=':
                    $where[] = [$k, $sym, (string)$v];
                    break;
                case 'LIKE':
                case 'NOT LIKE':
                case 'LIKE %...%':
                case 'NOT LIKE %...%':
                    $where[] = [$k, trim(str_replace('%...%', '', $sym)), "%{$v}%"];
                    break;
                case '>':
                case '>=':
                case '<':
                case '<=':
                    $where[] = [$k, $sym, intval($v)];
                    break;
                case 'FINDIN':
                case 'FINDINSET':
                    $where[] = "FIND_IN_SET('{$v}', " . ($this->relationSearch ? $k : '`' . str_replace('.', '`.`', $k) . '`') . ")";
                    break;
                case 'FIND_IN_SET':
                    $where[] = "FIND_IN_SET( " . ($this->relationSearch ? $k : '`' . str_replace('.', '`.`', $k) . '`') . ",'{$v}')";
                    break;
                case 'IN':
                case 'IN(...)':
                case 'NOT IN':
                case 'NOT IN(...)':
                    $where[] = [$k, str_replace('(...)', '', $sym), is_array($v) ? $v : explode(',', $v)];
                    break;
                case 'BETWEEN':
                case 'NOT BETWEEN':
                    $arr = array_slice(explode(',', $v), 0, 2);
                    if (stripos($v, ',') === false || !array_filter($arr))
                        continue;
                    //当出现一边为空时改变操作符
                    if ($arr[0] === '') {
                        $sym = $sym == 'BETWEEN' ? '<=' : '>';
                        $arr = $arr[1];
                    } else if ($arr[1] === '') {
                        $sym = $sym == 'BETWEEN' ? '>=' : '<';
                        $arr = $arr[0];
                    }
                    $where[] = [$k, $sym, $arr];
                    break;
                case 'RANGE':
                case 'NOT RANGE':
                    $v = str_replace(' - ', ',', $v);
                    $arr = array_slice(explode(',', $v), 0, 2);
                    if (stripos($v, ',') === false || !array_filter($arr))
                        continue;
                    //当出现一边为空时改变操作符
                    if ($arr[0] === '') {
                        $sym = $sym == 'RANGE' ? '<=' : '>';
                        $arr = $arr[1];
                    } else if ($arr[1] === '') {
                        $sym = $sym == 'RANGE' ? '>=' : '<';
                        $arr = $arr[0];
                    }
                    $where[] = [$k, str_replace('RANGE', 'BETWEEN', $sym) . ' time', $arr];
                    break;
                case 'LIKE':
                case 'LIKE %...%':
                    $where[] = [$k, 'LIKE', "%{$v}%"];
                    break;
                case 'NULL':
                case 'IS NULL':
                case 'NOT NULL':
                case 'IS NOT NULL':
                    $where[] = [$k, strtolower(str_replace('IS ', '', $sym))];
                    break;
                default:
                    break;
            }
        }
        $where = function ($query) use ($where) {
            foreach ($where as $k => $v) {
                if (is_array($v)) {
                    call_user_func_array([$query, 'where'], $v);
                } else {
                    $query->where($v);
                }
            }
        };
        return [$where, $sort, $order, $offset, $limit];
    }

}
